/*
 *接口编写处...
*如果接口需要做Action的权限验证，请在Action上使用属性
*如: [ApiActionPermission("big_data_production",Enums.ActionPermissionOptions.Search)]
 */
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.Entity.DomainModels;
using System.Linq;
using VOL.JIRUI.IServices;
using System.Collections;
using VOL.Core.DBManager;
using VOL.Core.Filters;
using Microsoft.AspNetCore.Authorization;
using System.Globalization;
using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime;
using VOL.Core.Extensions;
using Microsoft.IdentityModel.Tokens;
using Confluent.Kafka;

namespace VOL.JIRUI.Controllers
{
    public partial class big_data_productionController
    {
        private readonly Ibig_data_productionService _service;//访问业务代码
        private readonly IHttpContextAccessor _httpContextAccessor;

        [ActivatorUtilitiesConstructor]
        public big_data_productionController(
            Ibig_data_productionService service,
            IHttpContextAccessor httpContextAccessor
        )
        : base(service)
        {
            _service = service;
            _httpContextAccessor = httpContextAccessor;
        }

        /// <summary>
        /// 根据VIN去mes系统查询对应的生产数据
        /// </summary>
        /// <returns></returns>
        ///[ApiTask]
        [HttpGet,HttpPost, Route("get_mes_production"), AllowAnonymous]
        ///[ApiActionPermission()]
        public dynamic get_mes_production()
        {
            //   string ss = "SELECT * FROM (SELECT a.id,a.produceorder,to_char(to_date(d.welding_online_time,'yyyymmddhh24miss'),'yyyymmddhh24miss')welding_online_time,d.welding_online_operator,to_char(to_date(d.welding_online_time1,'yyyymmddhh24miss'),'yyyymmddhh24miss')welding_downline_time,d.welding_online_name1 welding_downline_operator,to_char(to_date(d.coating_online_time,'yyyymmddhh24miss'),'yyyymmddhh24miss') coating_online_time,d.coating_online_operator,to_char(to_date(d.coating_online_time1,'yyyymmddhh24miss'),'yyyymmddhh24miss')coating_downline_time,d.coating_online_name1 coating_downline_operator,to_char(to_date(c.finaling_online_time,'yyyymmddhh24miss'),'yyyymmddhh24miss')finaling_online_time,c.finaling_online_operator,to_char(to_date(c.finaling_downline_time,'yyyymmddhh24miss'),'yyyymmddhh24miss')finaling_downline_time,c.finaling_downline_operator,to_char(to_date(c.in_stock_time,'yyyymmddhh24miss'),'yyyymmddhh24miss')in_stock_date,c.in_stock_operator,b.body_code,b.welding_body_code,b.coating_body_code,to_char(to_date(a.REALEASETIME,'yyyymmddhh24miss'),'yyyymmddhh24miss') qc_date FROM ((SELECT a.id,a.produceorder,b.PORECEIVETIME,c.REALEASETIME FROM prodplan a INNER JOIN SEQPLAN b ON a.produceorder=b.produceorder LEFT JOIN QCERTIFICATEZC c ON a.id=substr(c.caridcode,10,18) AND c.carstatus='QX' and c.status='IS' WHERE a.lineno='100' and b.PORECEIVETIME >='20200101') a LEFT JOIN (SELECT * FROM (SELECT d.produceorder,a.lineno,a.model FROM PRODPLAN a INNER JOIN PRODPLAN d ON a.id=d.topcarid AND d.topcarid IS NOT NULL WHERE a.lineno IN ('300','400','800')) PIVOT (MAX(model) FOR LINENO IN ('800' AS body_code,'400' AS welding_body_code,'300' AS coating_body_code))) b ON a.produceorder=b.produceorder LEFT JOIN (SELECT * FROM (SELECT a.produceorder,b.time,CASE WHEN c.username IS NULL THEN b.userid ELSE c.username END username,b.facttype FROM PRODPLAN a LEFT JOIN (SELECT a.vin,a.facttype,a.userid,a.time FROM prodfact a INNER JOIN (SELECT vin,facttype,MAX(TIME) TIME FROM prodfact GROUP BY vin,facttype) b ON a.vin=b.vin AND a.facttype=b.facttype AND a.time=b.time) b ON a.vin=b.vin LEFT JOIN syses_userid c ON b.userid=c.userid WHERE a.lineno='100' AND b.facttype IN ('05','06','14')) PIVOT (MAX(TIME) TIME,MAX(username) operator FOR facttype IN ('14' AS finaling_online,'06' finaling_downline,'05' in_stock))) c ON a.produceorder=c.produceorder LEFT JOIN (SELECT * FROM (SELECT * FROM (SELECT b.time,CASE WHEN c.username IS NULL THEN b.userid ELSE c.username END username,d.produceorder,a.lineno,b.facttype FROM PRODPLAN a LEFT JOIN (SELECT a.vin,a.facttype,a.userid,a.time FROM prodfact a INNER JOIN (SELECT vin,facttype,MAX(TIME) TIME FROM prodfact GROUP BY vin, facttype) b ON a.vin=b.vin AND a.facttype=b.facttype AND a.time=b.time) b ON a.vin=b.vin LEFT JOIN syses_userid c ON b.userid=c.userid INNER JOIN PRODPLAN d ON a.id=d.topcarid AND d.topcarid IS NOT NULL WHERE a.lineno IN ('300','400') AND b.facttype IN ('01','02')) PIVOT (MAX(TIME) TIME,MAX(username) name FOR facttype IN ('01' AS LINE,'02' AS downline))) PIVOT (MAX(line_time) TIME,MAX(line_name) operator,MAX(downline_time) time1,MAX(downline_name) name1 FOR LINENO IN ('400' AS welding_online,'300' AS coating_online))) d ON a.produceorder=d.produceorder)) a where a.produceorder in "
            // 根据传递过来的VIN基础表中合格证时间为空的所有VIN去查找对应的生产信息
            List<big_data_basic> big_data_basicList = DBServerProvider.DbContext.Set<big_data_basic>().Where(x => x.certificate_printing_time == null && x.ERDAT >= DateTime.ParseExact("2021", "yyyy", CultureInfo.CurrentCulture) && x.AUFNR != "").ToList();
            // List<big_data_basic> big_data_basicList = DBServerProvider.DbContext.Set<big_data_basic>().Where(x => x.AUFNR == "1100071698").ToList();

            int maxNum = 300;
            int length = 300;
            int startIndex = 0;
            //因Vin码量可能会很大。所以需要循环几次执行
            if (big_data_basicList.Count() > 0)
            {
                int loopNum = big_data_basicList.Count() / length;
                if (big_data_basicList.Count() % length > 0)
                {
                    loopNum += 1;
                }

                for (int i = 1; i <= loopNum; i++)
                {
                    // 初期每次拼接200
                    maxNum = length * i;
                    string vinappend = "(";
                    if (maxNum > big_data_basicList.Count())
                    {
                        maxNum = big_data_basicList.Count() - 1;
                    }
                    for (int j = startIndex; j <= maxNum; j++)
                    {
                        if (big_data_basicList[j] != null && big_data_basicList[j].AUFNR != null)
                        {
                            vinappend += "'" + big_data_basicList[j].AUFNR + "',";
                        }
                    }

                    vinappend = vinappend.Substring(0, vinappend.Length - 1) + ")";

                    execute_sql_dictionary execute_sql_dictionary = DBServerProvider.DbContext.Set<execute_sql_dictionary>().Where(x => (x.classify_route == "get_mes_production")).FirstOrDefault();
                    //拼接sql
                    execute_sql_dictionary.sql = execute_sql_dictionary.sql + vinappend;
                    execute_sql_dictionary.sql = execute_sql_dictionary.sql.Replace("'", "''");
                    List<dynamic> dditem = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery(MES,'" + execute_sql_dictionary.sql + "')", "").ToList(); ;//订单明细,通过dblink取数，SQL运行的是oracle预警
                    foreach (var item in dditem)
                    {
                        string temp = item.PRODUCEORDER;

                        big_data_production big_data_production2 = DBServerProvider.DbContext.Set<big_data_production>().Where(x => x.produceorder == temp).FirstOrDefault();
                        if (big_data_production2.IsNullOrEmpty())
                        {
                            big_data_production big_data_production = new big_data_production();
                            big_data_production.vin = item.ID;
                            big_data_production.produceorder = item.PRODUCEORDER;
                            big_data_production.welding_online_time = item.WELDING_ONLINE_TIME == null ? null : DateTime.ParseExact(item.WELDING_ONLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production.welding_online_operator = item.WELDING_ONLINE_OPERATOR;
                            big_data_production.welding_downline_time = item.WELDING_DOWNLINE_TIME == null ? null : DateTime.ParseExact(item.WELDING_DOWNLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production.welding_downline_operator = item.WELDING_DOWNLINE_OPERATOR;
                            big_data_production.coating_online_time = item.COATING_ONLINE_TIME == null ? null : DateTime.ParseExact(item.COATING_ONLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production.coating_online_operator = item.COATING_ONLINE_OPERATOR;
                            big_data_production.coating_downline_time = item.COATING_DOWNLINE_TIME == null ? null : DateTime.ParseExact(item.COATING_DOWNLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production.coating_downline_operator = item.COATING_DOWNLINE_OPERATOR;
                            big_data_production.finaling_online_time = item.FINALING_ONLINE_TIME == null ? null : DateTime.ParseExact(item.FINALING_ONLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production.finaling_online_operator = item.FINALING_ONLINE_OPERATOR;
                            big_data_production.finaling_downline_time = item.FINALING_DOWNLINE_TIME == null ? null : DateTime.ParseExact(item.FINALING_DOWNLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production.finaling_downline_operator = item.FINALING_DOWNLINE_OPERATOR;
                            big_data_production.in_stock_date = item.IN_STOCK_DATE == null ? null : DateTime.ParseExact(item.IN_STOCK_DATE, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production.in_stock_operator = item.IN_STOCK_OPERATOR;
                            big_data_production.welding_product_plant_name = "焊装车间";
                            big_data_production.welding_product_name = "焊装";
                            big_data_production.coating_product_plant_name = "涂装车间";
                            big_data_production.coating_product_line_name = "涂装";
                            big_data_production.finaling_product_plant_name = "总装车间";
                            big_data_production.finaling_product_line_name = "总装";
                            big_data_production.body_code = item.BODY_CODE;
                            big_data_production.welding_body_code = item.WELDING_BODY_CODE;
                            big_data_production.coating_body_code = item.COATING_BODY_CODE;
                            big_data_production.qc_date = item.QC_DATE == null ? null : DateTime.ParseExact(item.QC_DATE, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production.Creator = "MES";
                            big_data_production.CreateDate = DateTime.Now;
                            DBServerProvider.DbContext.Add(big_data_production);
                        }
                        else
                        {
                            big_data_production2.welding_online_time = item.WELDING_ONLINE_TIME == null ? null : DateTime.ParseExact(item.WELDING_ONLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production2.welding_online_operator = item.WELDING_ONLINE_OPERATOR;
                            big_data_production2.welding_downline_time = item.WELDING_DOWNLINE_TIME == null ? null : DateTime.ParseExact(item.WELDING_DOWNLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production2.welding_downline_operator = item.WELDING_DOWNLINE_OPERATOR;
                            big_data_production2.coating_online_time = item.COATING_ONLINE_TIME == null ? null : DateTime.ParseExact(item.COATING_ONLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production2.coating_online_operator = item.COATING_ONLINE_OPERATOR;
                            big_data_production2.coating_downline_time = item.COATING_DOWNLINE_TIME == null ? null : DateTime.ParseExact(item.COATING_DOWNLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production2.coating_downline_operator = item.COATING_DOWNLINE_OPERATOR;
                            big_data_production2.finaling_online_time = item.FINALING_ONLINE_TIME == null ? null : DateTime.ParseExact(item.FINALING_ONLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production2.finaling_online_operator = item.FINALING_ONLINE_OPERATOR;
                            big_data_production2.finaling_downline_time = item.FINALING_DOWNLINE_TIME == null ? null : DateTime.ParseExact(item.FINALING_DOWNLINE_TIME, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production2.finaling_downline_operator = item.FINALING_DOWNLINE_OPERATOR;
                            big_data_production2.in_stock_date = item.IN_STOCK_DATE == null ? null : DateTime.ParseExact(item.IN_STOCK_DATE, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production2.in_stock_operator = item.IN_STOCK_OPERATOR;
                            big_data_production2.qc_date = item.QC_DATE == null ? null : DateTime.ParseExact(item.QC_DATE, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                            big_data_production2.Modifier = "MES";
                            big_data_production2.ModifyDate = DateTime.Now;
                          //  Console.WriteLine(big_data_production2.produceorder);
                            DBServerProvider.DbContext.Update(big_data_production2);
                        }
                        if ((item != null && item.QC_DATE != null) || (item != null && item.IN_STOCK_DATE != null))
                        {
                            /*big_data_basic big_data_basic = DBServerProvider.DbContext.Set<big_data_basic>().Where(x => x.AUFNR == temp).FirstOrDefault();*/
                            var ModifyDate = DateTime.Now;
                            var AUFNR = temp;
                            if (item.IN_STOCK_DATE != null)
                            {
                                var in_stock_date = item.IN_STOCK_DATE == null ? null : DateTime.ParseExact(item.IN_STOCK_DATE, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                                var sql1 = "update big_data_basic set in_stock_date = @in_stock_date,Modifier='MES',ModifyDate=@ModifyDate,status = 0 where AUFNR = @AUFNR";
                                DBServerProvider.SqlDapper.ExcuteNonQuery(sql1, new { in_stock_date, ModifyDate, AUFNR });
                            }
                            if (item.QC_DATE != null)
                            {
                                var certificate_printing_time = item.QC_DATE == null ? null : DateTime.ParseExact(item.QC_DATE, "yyyyMMddHHmmss", CultureInfo.CurrentCulture);
                                var sql1 = "update big_data_basic set certificate_printing_time =@certificate_printing_time,Modifier='MES',ModifyDate=@ModifyDate,status = 0 where AUFNR = @AUFNR";
                                DBServerProvider.SqlDapper.ExcuteNonQuery(sql1, new {  certificate_printing_time, ModifyDate, AUFNR });
                            }
                        }
                    }
                    startIndex = maxNum + 1;
                }
            }
            DBServerProvider.DbContext.SaveChanges();
            return big_data_basicList.Count();
        }
    }
}
